package ca.ualberta.cs.CLRDB.conference;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

import ca.ualberta.cs.CLRDB.DatabaseController;
import ca.ualberta.cs.CLRDB.interfaces.EntityRelManagement;
import ca.ualberta.cs.DB2ExceptionHandling.DB2SqlException;

public class PhaseManagement extends EntityRelManagement{

	private String phaseKeys = "";
	private String phaseRoleKeys = "";
	
	public PhaseManagement() {
		mainTableName = "PhaseRoles";
	}
	
	@Override
	public void initialize(Statement statement) throws SQLException {
		
		try{
			statement.executeUpdate("DROP TABLE Phases");
			statement.executeUpdate("DROP TABLE PhaseRoles");
		}catch(SQLException e){}
		
		statement.executeUpdate("CREATE TABLE Phases ( " +				
				"phase VARCHAR(20) NOT NULL, " +				
				"startTime DATE NOT NULL, " +
				"endTime DATE NOT NULL, " +
				"CONSTRAINT pk_phase PRIMARY KEY(phase) " +								
				") IN CLRSPACE");
	
		phaseKeys = "phase, startTime, endTime";
		
		setupDefaultPhaseTimes(statement);
		
		statement.executeUpdate("CREATE TABLE PhaseRoles ( " +				
				"phase VARCHAR(20) NOT NULL, " +	
				"role VARCHAR(10) NOT NULL, " +
				"canRate CHAR(1), " +
				"canComment CHAR(1), " +
				"seeComments CHAR(1), " +
				"canTag CHAR(1), " +
				"canUploadPaper CHAR(1), " +
				"seeProfiles CHAR(1), " +
				"seeAggregatedRate CHAR(1), " +
				"seeIndivRate CHAR(1), " +
				"canMakeDecision CHAR(1), " +
				"canFilterComments CHAR(1), " +
				//"can CHAR(1), " +
				"CONSTRAINT pk_phase_role PRIMARY KEY(phase,role), " +				
				"CONSTRAINT FK_phase FOREIGN KEY (phase) " +
            		"REFERENCES Phases (phase) ON DELETE CASCADE, " +				
            	"CONSTRAINT FK_role FOREIGN KEY (role) " +
            		"REFERENCES Roles (role) ON DELETE CASCADE " +
				") IN CLRSPACE");
		numOfCols = 12;
		
		phaseRoleKeys = "phase,role, canRate, canComment, seeComments, " +
						"canTag, canUploadPaper, seeProfiles, seeAggregatedRate, " +
						"seeIndivRate, canMakeDecision, canFilterComments";
		
		setupDefaultPhaseProperties(statement);
	}
	
	private void setupDefaultPhaseTimes(Statement statement){		
		
		try {
			statement.executeUpdate("INSERT INTO Phases ("+phaseKeys+") VALUES (" +
					"'paperupload', current date, current date + 2 days" +
					")");
			statement.executeUpdate("INSERT INTO Phases ("+phaseKeys+") VALUES (" +
					"'assignment', current date + 3 days, current date + 5 days" +
					")");
			statement.executeUpdate("INSERT INTO Phases ("+phaseKeys+") VALUES (" +
					"'review', current date + 6 days, current date + 8 days" +
					")");
			statement.executeUpdate("INSERT INTO Phases ("+phaseKeys+") VALUES (" +
					"'authorresponse', current date + 9 days, current date + 12 days" +
					")");
			statement.executeUpdate("INSERT INTO Phases ("+phaseKeys+") VALUES (" +
					"'decision', current date + 13 days, current date + 15 days" +
					")");
			statement.executeUpdate("INSERT INTO Phases ("+phaseKeys+") VALUES (" +
					"'notification', current date + 16 days, current date + 18 days" +
					")");
			/*statement.executeUpdate("INSERT INTO Phases ("+phaseKeys+") VALUES (" +
					"'allsee', current date + 19 days, current date + 22 days" +
					")");*/
			statement.executeUpdate("INSERT INTO Phases ("+phaseKeys+") VALUES (" +
					"'opendiscussion', current date + 23 days, current date + 29 days" +
					")");
		} catch (SQLException e) {
			DB2SqlException.printSqlErrorMsg(e);
		}
	}
	
	void setupPhaseTimes(Statement statement){
		
	}
	
	private void setupDefaultPhaseProperties(Statement statement){
		
		
		
		try {
			statement.executeUpdate("INSERT INTO PhaseRoles ("+phaseRoleKeys+") VALUES (" +
					"'paperupload','pcchair', " +
					"'0', '0', '0', '1', '0', '1', '0', '0', '0', '0'" +
					")");
			statement.executeUpdate("INSERT INTO PhaseRoles ("+phaseRoleKeys+") VALUES (" +
					"'paperupload','author', " +
					"'0', '0', '0', '1', '1', '0', '0', '0', '0', '0'" +
					")");
			statement.executeUpdate("INSERT INTO PhaseRoles ("+phaseRoleKeys+") VALUES (" +
					"'paperupload','reviewer', " +
					"'0', '0', '0', '0', '0', '0', '0', '0', '0', '0'" +
					")");
			
			
			//by default: only pcchair can assign papers. But, it should be possible to let reviewers choose and assign paper
			statement.executeUpdate("INSERT INTO PhaseRoles ("+phaseRoleKeys+") VALUES (" +
					"'assignment','pcchair', " +
					"'0', '0', '0', '1', '0', '1', '0', '0', '0', '0'" +
					")");
			
			statement.executeUpdate("INSERT INTO PhaseRoles ("+phaseRoleKeys+") VALUES (" +
					"'review','pcchair', " +
					"'1', '1', '1', '1', '0', '1', '1', '1', '0', '1'" +
					")");
			statement.executeUpdate("INSERT INTO PhaseRoles ("+phaseRoleKeys+") VALUES (" +
					"'review','author', " +
					"'0', '0', '0', '0', '0', '0', '0', '0', '0', '0'" +
					")");
			statement.executeUpdate("INSERT INTO PhaseRoles ("+phaseRoleKeys+") VALUES (" +
					"'review','reviewer', " +
					"'1', '1', '1', '1', '0', '1', '1', '1', '0', '1'" +
					")");

			
			statement.executeUpdate("INSERT INTO PhaseRoles ("+phaseRoleKeys+") VALUES (" +
					"'authorresponse','pcchair', " +
					"'0', '1', '1', '1', '0', '1', '1', '1', '0', '0'" +
					")");
			statement.executeUpdate("INSERT INTO PhaseRoles ("+phaseRoleKeys+") VALUES (" +
					"'authorresponse','author', " +
					"'0', '1', '1', '1', '0', '1', '1', '0', '0', '0'" +
					")");
			statement.executeUpdate("INSERT INTO PhaseRoles ("+phaseRoleKeys+") VALUES (" +
					"'authorresponse','reviewer', " +
					"'0', '1', '1', '1', '0', '1', '1', '1', '0', '0'" +
					")");
			
			
			statement.executeUpdate("INSERT INTO PhaseRoles ("+phaseRoleKeys+") VALUES (" +
					"'decision','pcchair', " +
					"'1', '1', '1', '1', '0', '1', '1', '1', '1', '1'" +
					")");
			statement.executeUpdate("INSERT INTO PhaseRoles ("+phaseRoleKeys+") VALUES (" +
					"'decision','author', " +
					"'0', '0', '0', '0', '0', '0', '0', '0', '0', '0'" +
					")");
			statement.executeUpdate("INSERT INTO PhaseRoles ("+phaseRoleKeys+") VALUES (" +
					"'decision','reviewer', " +
					"'1', '1', '1', '1', '0', '1', '1', '1', '1', '1'" +
					")");
			
			
			statement.executeUpdate("INSERT INTO PhaseRoles ("+phaseRoleKeys+") VALUES (" +
					"'notification','pcchair', " +
					"'0', '0', '1', '0', '0', '1', '1', '1', '0', '0'" +
					")");
			statement.executeUpdate("INSERT INTO PhaseRoles ("+phaseRoleKeys+") VALUES (" +
					"'notification','author', " +
					"'0', '0', '1', '0', '0', '1', '1', '1', '0', '0'" +
					")");
			statement.executeUpdate("INSERT INTO PhaseRoles ("+phaseRoleKeys+") VALUES (" +
					"'notification','reviewer', " +
					"'0', '0', '1', '0', '0', '1', '1', '1', '0', '0'" +
					")");
			
			/*String s = "phase,role, " +
			"canRate, canComment, seeComments, " +
			"canTag, canUploadPaper, seeProfiles, seeAggregatedRate, " +
			"seeIndivRate, canMakeDecision, canFilterComments";*/
			
			statement.executeUpdate("INSERT INTO PhaseRoles ("+phaseRoleKeys+") VALUES (" +
					"'opendiscussion','pcchair', " +
					"'1', '1', '1', '1', '0', '1', '1', '1', '0', '0'" +
					")");
			statement.executeUpdate("INSERT INTO PhaseRoles ("+phaseRoleKeys+") VALUES (" +
					"'opendiscussion','author', " +
					"'1', '1', '1', '1', '0', '1', '1', '1', '0', '0'" +
					")");
			statement.executeUpdate("INSERT INTO PhaseRoles ("+phaseRoleKeys+") VALUES (" +
					"'opendiscussion','reviewer', " +
					"'1', '1', '1', '1', '0', '1', '1', '1', '0', '0'" +
					")");
		} catch (SQLException e) {
			DB2SqlException.printSqlErrorMsg(e);
		}
	}

	void setupPhaseProperties(Statement statement){
		
	}
	
	boolean getPhaseRolePermission(String action, Statement statement){
		boolean permit = true;
		boolean resFound = false;
		
		boolean innerConnection = false;
		Connection con = null;
		
		try{
			
			if(statement == null){
				innerConnection = true;
				con = DatabaseController.connectToDB();
				statement = con.createStatement();
			}
			
			ResultSet res = null;
			if(action.equalsIgnoreCase("addcomment")){
				res = statement.executeQuery("SELECT canComment FROM Phases ph, PhaseRoles pr " +
					"WHERE startTime <= current date AND endTime >= current date " +
					"AND role = '"+role+"'");
							
				while(res.next()){ //if phases have overlap: if one gives permission: then permit
					permit = permit && res.getBoolean("canComment");
					resFound = true;
				}
			}
			
			if(innerConnection){
				statement.close();
				con.commit();
				con.close();
			}
			
		}catch(SQLException e){
			DB2SqlException.printSqlErrorMsg(e);
			if(innerConnection){
				if(con != null){
					try {
						statement.close();					
						con.commit();
						con.close();
					} catch (SQLException e1) {
						DB2SqlException.printSqlErrorMsg(e1);
					}
				}
			}
		}
		
		
		return permit && resFound;
	}

}
